
/* ***
    0001_vw_CustomerOrdersProducts.sql
*/

/*
   View for CustomerOrdersForm.
*/
if exists(select * from dbo.sysobjects where 
   id = object_id(N'[dbo].[vw_CustomerOrdersProducts]') and
   OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_CustomerOrdersProducts]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_CustomerOrdersProducts]
AS
SELECT dbo.Customer.ID AS CustomerId, dbo.[Order].ID AS OrderId, 
       dbo.Product.ID AS ProductId, dbo.Product.Name, 
       dbo.OrderDetail.UnitPrice, 
       dbo.OrderDetail.Quantity, dbo.OrderDetail.Discount, 
       dbo.OrderDetail.UnitPrice * dbo.OrderDetail.Quantity *
           (1-[Discount]) AS ExtendedPrice
FROM   dbo.Customer INNER JOIN
       dbo.[Order] ON dbo.Customer.ID = dbo.[Order].CustomerId INNER JOIN
       dbo.OrderDetail ON dbo.[Order].ID = dbo.OrderDetail.OrderID INNER JOIN
       dbo.Product ON dbo.OrderDetail.ProductID = dbo.Product.ID
GO

/* ***
    0002_vw_CustomerOrderTotals.sql
*/

/*
   CustomerOrderTotals view
*/
if exists(select * from dbo.sysobjects where 
   id = object_id(N'[dbo].[vw_CustomerOrderTotals]') and
   OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_CustomerOrderTotals]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_CustomerOrderTotals]
AS
select
  s.CustomerId, 
  s.OrderId,
  s.TotalExtendedPrice,
  o.Freight,
  (s.TotalExtendedPrice + o.Freight) as OrderTotal   
	from [Order] o,
	(
	  select 
	   CustomerId, OrderId, 
       Sum(ExtendedPrice) as TotalExtendedPrice
	     from vw_customerOrdersProducts
	   group by CustomerId, OrderId
	) s

	where o.Id = s.OrderId
GO


/* ***
    0003_vw_CustomerCountries.sql
*/

/*
   CustomerCountries view
*/
if exists(select * from dbo.sysobjects where 
   id = object_id(N'[dbo].[vw_CustomerCountries]') and
   OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_CustomerCountries]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_CustomerCountries]
AS
SELECT DISTINCT Country FROM Customer
GO


/* ***
    0004_vw_AlphabeticalListOfProductsReport.sql
*/


/*
   AlphabeticalListOfProductsReport view
*/

if exists(select * from dbo.sysobjects where 
   id = object_id(N'[dbo].[vw_AlphabeticalListOfProductsReport]') and
   OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_AlphabeticalListOfProductsReport]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vw_AlphabeticalListOfProductsReport]
AS
select SUBSTRING(p.Name,1,1) as SortGroupLetter, 
       p.Name as ProductName, 
       c.Name as CategoryName, 
       p.QuantityPerUnit, p.UnitsInStock
from Category c, Product p
where c.ID = p.CategoryId
GO



/* ***
    0005_sp_SalesTotalsByAmountReport.sql
*/

if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SalesTotalsByAmountReport]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SalesTotalsByAmountReport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure [sp_SalesTotalsByAmountReport]

As

Set NoCount On

SELECT 
  [OrderSubTotals].Subtotal AS SaleAmount, 
  [Order].ID, 
  Customer.Name, 
  [Order].ShippedDate,
  CAST(FLOOR([OrderSubTotals].Subtotal/1000) as int) AS GroupValue
FROM Customer INNER JOIN [Order]
   ON Customer.ID = [Order].CustomerID
inner join
(
SELECT 
  [OrderDetail].OrderID, 
  Sum(Cast([UnitPrice]*[Quantity]*(1-[Discount])/100  as smallmoney)*100) AS Subtotal
FROM [OrderDetail]
GROUP BY [OrderDetail].OrderID
) OrderSubTotals on [Order].ID = OrderSubTotals.OrderId 
WHERE ([OrderSubtotals].Subtotal >2500) AND 
      ([Order].ShippedDate Between '1997-01-01' And '1997-12-31')
order by [OrderSubTotals].Subtotal desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/* ***
    0006_sp_SummaryOfSalesByYearReport.sql
*/

if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SummaryOfSalesByYearReport]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SummaryOfSalesByYearReport]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[sp_SummaryOfSalesByYearReport]

As

Set NoCount On
-- Summary of Sales by Year

select
YearNum,
[QuarterNum],
Count(OrderId) as OrdersCount,
SUM(Subtotal) as SalesAmount

from
(
Select 
YEAR(ShippedDate) as [YearNum], 
'QuarterNum' = CASE 
  when MONTH(ShippedDate) between 1 and 3 then 1
  when MONTH(ShippedDate) between 4 and 6 then 2
  when MONTH(ShippedDate) between 7 and 9 then 3
  when MONTH(ShippedDate) between 10 and 12 then 4
END,
OrderId,
Subtotal 
from
(
	SELECT 
	   [Order].ShippedDate, 
       [Order].ID as OrderID, 
       [OrderSubtotals].Subtotal
	FROM [Order] INNER JOIN
	(
		SELECT 
		  [OrderDetail].OrderID, 
		  Sum(Cast([UnitPrice]*[Quantity]*(1-[Discount])/100  
			as smallmoney)*100) AS Subtotal
		FROM [OrderDetail]
		GROUP BY [OrderDetail].OrderID
	) OrderSubTotals 
	ON [Order].ID = [OrderSubtotals].OrderID
	WHERE ([Order].ShippedDate Is Not Null)
) r
) r1
group by 
YearNum,
[QuarterNum]
order by 
YearNum,
[QuarterNum]
GO


/* ***
    0007_sp_SummaryOfSalesByQuarterReport.sql
*/

if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SummaryOfSalesByQuarterReport]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SummaryOfSalesByQuarterReport]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[sp_SummaryOfSalesByQuarterReport]
As
Set NoCount On

select
[QuarterNum],
YearNum,
Count(OrderId) as OrdersCount,
SUM(Subtotal) as SalesAmount

from
(
Select 
YEAR(ShippedDate) as [YearNum], 
'QuarterNum' = CASE 
  when MONTH(ShippedDate) between 1 and 3 then 1
  when MONTH(ShippedDate) between 4 and 6 then 2
  when MONTH(ShippedDate) between 7 and 9 then 3
  when MONTH(ShippedDate) between 10 and 12 then 4
END,
OrderId,
Subtotal 
from
(
	SELECT 
	   [Order].ShippedDate, 
       [Order].ID as OrderID, 
       [OrderSubtotals].Subtotal
	FROM [Order] INNER JOIN
	(
		SELECT 
		  [OrderDetail].OrderID, 
		  Sum(Cast([UnitPrice]*[Quantity]*(1-[Discount])/100  
			as smallmoney)*100) AS Subtotal
		FROM [OrderDetail]
		GROUP BY [OrderDetail].OrderID
	) OrderSubTotals 
	ON [Order].ID = [OrderSubtotals].OrderID
	WHERE ([Order].ShippedDate Is Not Null)
) r
) r1
group by 
[QuarterNum],
YearNum
order by 
[QuarterNum],
YearNum
GO


/* ***
    0008_sp_CatalogReport.sql
*/

if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_CatalogReport]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_CatalogReport]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[sp_CatalogReport]
As
Set NoCount On

select Category.ID as CategoryId, 
       Category.Name as CategoryName,
       Category.Description,
       Category.Picture,
       Product.ID as ProductId,
       Product.Name as ProductName,
       Product.QuantityPerUnit,
       Product.UnitPrice
from Category, Product
where Category.ID = Product.CategoryId
GO
        




/* ***
    0009_sp_Invoices.sql
*/

if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_Invoices]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Invoices]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.sp_Invoices
	(
	@OrderID int = Null
	)
AS
SET NOCOUNT ON
SELECT      [OrderDetail].OrderId as [ID], -- [Order].ID, 
            [Order].CustomerId, [Order].ShipName, [Order].ShipAddress, 
            [Order].ShipCity, [Order].ShipRegion, [Order].ShipPostalCode, 
            [Order].ShipCountry, [Order].OrderDate, [Order].RequiredDate, 
            [Order].ShippedDate, OrderDetail.UnitPrice, 
            OrderDetail.Quantity, OrderDetail.Discount, 
            CONVERT(money, (OrderDetail.UnitPrice * OrderDetail.Quantity) * (1 - OrderDetail.Discount) / 100 * 100) AS ExtendedPrice, 
            [Order].Freight, OrderDetail.ProductID, [Order].ShipperId, 
            [Order].EmployeeID, 
            OrderDetail.OrderID, 
            Employee.FirstName + N' ' + Employee.LastName AS SalesPerson, 
            Customer.ContactName, Customer.Address, 
            Customer.City, Customer.Region, Customer.PostalCode, 
            Customer.Country, Product.Name AS ProductName, 
            Shipper.Name AS ShipVia
FROM        [Order] INNER JOIN
                  OrderDetail ON [Order].ID = OrderDetail.OrderID INNER JOIN
                  Customer ON [Order].CustomerId = Customer.ID INNER JOIN
                  Employee ON [Order].EmployeeID = Employee.ID INNER JOIN
                  Product ON OrderDetail.ProductID = Product.ID INNER JOIN
                  Shipper ON [Order].ShipperId = Shipper.ID
WHERE     (@OrderID IS NULL) OR
                  (@OrderID = [Order].ID)

GO

/* ***
    0010_sp_ListOrders.sql
*/

if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_ListOrders]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ListOrders]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE dbo.sp_ListOrders
	(
	@orderId int = null
	)
/*
  Sample:
   sp_ListOrders       - list all orders
   sp_ListOrders 10248 - list Order with Order.ID = 10248
*/	
AS
	SET NOCOUNT ON
	
	select 
	 [OrderDetail].OrderId, 
	 [Order].OrderDate,
	 [Product].Name, 
	 [OrderDetail].Quantity
	 from [Order],[OrderDetail],[Product]
	 where
	  [Order].ID = OrderDetail.OrderId and
	  [OrderDetail].ProductId = Product.ID and
	  (@orderId is null or @orderId = [Order].ID)
GO


/* ***
    0011_sp_SalesAnalysisReport.sql
*/

if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SalesAnalysisReport]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SalesAnalysisReport]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[sp_SalesAnalysisReport]
As
Set NoCount On
SELECT 
 Employee.Country,
 Employee.LastName, 
 Employee.FirstName, 
 YEAR(ShippedDate) as [ShippedYearNum], 
 'ShippedQuarterNum' = CASE 
  when MONTH(ShippedDate) between 1 and 3 then 1
  when MONTH(ShippedDate) between 4 and 6 then 2
  when MONTH(ShippedDate) between 7 and 9 then 3
  when MONTH(ShippedDate) between 10 and 12 then 4
 END,
 MONTH(ShippedDate) as [ShippedMonthNum], 
 [Order].ShippedDate, 
 [Order].ID as OrderID,
 [OrderSubtotals].Subtotal AS SaleAmount 
 FROM Employee, [Order],
	(
		SELECT 
		  [OrderDetail].OrderID, 
		  Sum(Cast([UnitPrice]*[Quantity]*(1-[Discount])/100  
			as smallmoney)*100) AS Subtotal
		FROM [OrderDetail]
		GROUP BY [OrderDetail].OrderID
	) OrderSubTotals 
 where
   [Order].ID=[OrderSubtotals].OrderID and 
   Employee.ID=[Order].EmployeeID and
   not ([Order].ShippedDate is null)
order by 
 Employee.LastName, 
 Employee.FirstName,
[Order].ShippedDate, 
[Order].ID
GO



/* ***
    0012_udf_OrderDetailsExtended.sql
*/

if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[udf_OrderDetailsExtended]') and 
      xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_OrderDetailsExtended]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION udf_OrderDetailsExtended 
(	
)
-- select * from udf_OrderDetailsExtended()
RETURNS TABLE 
AS
RETURN 
(
	SELECT 
	[OrderDetail].OrderID, 
	[OrderDetail].ProductID, 
	Product.Name as ProductName, 
	[OrderDetail].UnitPrice, 
	[OrderDetail].Quantity, 
	[OrderDetail].Discount, 
	Cast(([OrderDetail].UnitPrice*[Quantity]*(1-[Discount])/100)*100 as money) AS ExtendedPrice
	FROM Product, [OrderDetail] 
	where Product.ID = [OrderDetail].ProductID
)
GO


/* ***
    0013_sp_SalesByCategory.sql
*/

if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SalesByCategory]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SalesByCategory]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE sp_SalesByCategory
   @categoryId nvarchar(20) = null	
AS
BEGIN
	SET NOCOUNT ON;
	SELECT 
	  Product.CategoryID, 
	  Category.Name as CategoryName, 
	  Product.Name as ProductName, 
	  Sum([OrderDetailsExtended].ExtendedPrice) AS ProductSales
	FROM Category, 
		 Product,
		 [Order],
		 (select * from udf_OrderDetailsExtended()) as [OrderDetailsExtended] 
	where [Order].ID = [OrderDetailsExtended].OrderID and
		   Product.ID = [OrderDetailsExtended].ProductID and
		   Category.ID = Product.CategoryID and
           (@categoryId is null or Category.ID = @categoryId)
	GROUP BY Product.CategoryID, 
			 Category.Name, 
			 Product.Name
	ORDER BY Category.Name

END
GO

/* ***
    0014_udf_OrderSubTotals.sql
*/

if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[udf_OrderSubTotals]') and 
      xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_OrderSubTotals]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION udf_OrderSubTotals
(	
)
-- select * from udf_OrderSubTotals()
RETURNS TABLE 
AS
RETURN 
(
  SELECT 
	[OrderDetail].OrderID, 
	Sum(Cast([UnitPrice]*[Quantity]*(1-[Discount])/100 as money)*100) AS Subtotal
	FROM [OrderDetail]
	GROUP BY [OrderDetail].OrderID
)
GO


/* ***
    0015_sp_SalesByYear.sql
*/
if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SalesByYear]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SalesByYear]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE sp_SalesByYear
   @yearNum int = 1997
AS
BEGIN
	SET NOCOUNT ON;
SELECT 
  [Order].ShippedDate, 
  [OrderSubtotals].OrderID, 
  [OrderSubtotals].Subtotal,
  'QuarterNum' = CASE 
  when MONTH(ShippedDate) between 1 and 3 then 1
  when MONTH(ShippedDate) between 4 and 6 then 2
  when MONTH(ShippedDate) between 7 and 9 then 3
  when MONTH(ShippedDate) between 10 and 12 then 4
  END,    
  Year([ShippedDate]) AS [Year]
FROM 
   [Order],
   (select * from udf_OrderSubtotals()) as [OrderSubtotals] 
    where 
     [Order].ID = [OrderSubtotals].OrderID and
     (Year([Order].ShippedDate) = @yearNum)
END
GO
